package com.tank.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.tank.bean.User;
import com.tank.dao.BuddyDAO;
import com.tank.util.NoticeUtil;
import com.tank.util.Pager;
import com.tank.util.TankUtil;
import com.tank.vo.Buddy;

@Repository("buddyDao")
public class BuddyDaoImpl extends TankDaoSupport implements BuddyDAO {

	public Pager findFansList(User master) {
		
//		Session session=this.getSession();
//		
//		//String hql="select new com.tank.vo.Buddy(u.name,(case when u in elements(m.follows) then true else false end)) from User u,User m where u in elements(m.fans) and m.id="+master.getId();
//		String hql="select new com.tank.vo.Buddy(u.name,(case when u in elements(m.follows) then true else false end)) from User u,User m where u in elements(m.fans) and m.id="+master.getId();
//		
//		System.out.println("===++++");
//		// in elements(m.follows)
//		Query q=session.createQuery(hql);
//		
//		List ls=q.list();
//		System.out.println("===++++");
//		session.close();
//		return  ls;
		
		final Integer id=master.getId();
		
		
		return   this.getHibernateTemplate().execute(new HibernateCallback<Pager>() {

			public Pager doInHibernate(Session session) throws HibernateException,
					SQLException {
				System.out.println(id);
				Pager pager=new Pager();
				String sql="select count(u.id) from user u left join buddy b on (b.tid=u.id and b.fid=:id) where u.id in (select fid from buddy where tid=:id)";
				Query qc=session.createSQLQuery(sql).setInteger("id", id);
				
				sql="select u.id,u.name,u.photo,b.tid from user u left join buddy b on (b.tid=u.id and b.fid=:id) where u.id in (select fid from buddy where tid=:id)";
				Query q=session.createSQLQuery(sql).setInteger("id", id);
				
				
				pager.setRowCount(qc.uniqueResult());
				List<Object[]> ls=pager.pageQuery(q);
				System.out.println(ls.size());
				List<Buddy> bs=new ArrayList(ls.size());
				boolean flag=false;
				for(Object[] os:ls){
					flag=(os[3]==null)?false:true;
					bs.add(new Buddy((Integer)os[0],(String)os[1],(String)os[2],flag));					
				}
				pager.setData(bs);
				session.clear();
				session.close();

				return pager;
			}
			});
		}

	public Pager findFollowsList(User master) {
		final Integer id=master.getId();
		return this.getHibernateTemplate().execute(new HibernateCallback<Pager>() {

			public Pager doInHibernate(Session session) throws HibernateException,
					SQLException {
				Pager pager=new Pager();
				String sql="select u.id,u.name,u.photo,b.fid from user u left join buddy b on (b.fid=u.id and b.tid=:id) where u.id in (select tid from buddy where fid=:id) ";
				Query q=session.createSQLQuery(sql).setInteger("id", id);
				String sql2="select count(u.id) from user u left join buddy b on (b.fid=u.id and b.tid=:id) where u.id in (select tid from buddy where fid=:id) ";
				Query qc=session.createSQLQuery(sql2).setInteger("id", id);
				
				pager.setRowCount(qc.uniqueResult());
				List<Object[]> ls=pager.pageQuery(q);
				
				System.out.println("============="+ls.size());
//				System.out.println("======");
//				System.out.println("="+getSession().getClass().getName());
//				System.out.println("======");
				
				List<Buddy> bs=new ArrayList(ls.size());
				boolean flag=false;
				for(Object[] os:ls){
					flag=(os[3]==null)?false:true;
					bs.add(new Buddy((Integer)os[0],(String)os[1],(String)os[2],flag));					
				}
				pager.setData(bs);	
//				System.out.println("======");
//				System.out.println("="+session.getClass().getName());
//				System.out.println("======");
				
				
				session.clear();
				session.close();
				
				return pager;
			}
		});
	}
	
	public List<Buddy> findBuddyList(User master) {
		final Integer id=master.getId();
		final String name=master.getName();
		return this.getHibernateTemplate().executeFind(new HibernateCallback<List>() {

			public List doInHibernate(Session session) throws HibernateException,
					SQLException {
				System.out.println(id);
				String sql="select u.id,u.name,u.photo,b.fid from user u left join buddy b on (b.fid=u.id and b.tid=:id) where u.name like :name ";
				List<Object[]> ls=session.createSQLQuery(sql).setInteger("id", id).setString("name", "%"+name+"%").list();
				
				System.out.println(ls.size());
				
				
				List<Buddy> bs=new ArrayList(ls.size());
				boolean flag=false;
				for(Object[] os:ls){
					flag=(os[3]==null)?false:true;
					bs.add(new Buddy((Integer)os[0],(String)os[1],(String)os[2],flag));					
				}

				session.clear();
				session.close();

				return bs;
			}
		});
	}
	public boolean follow(User master, User target) {
		return add(master, target);
	}

	public boolean drop(User master, User target) {
		return remove(master, target);
	}



	public boolean add(User master, User target) {
		Session session=this.getSession();
		String sql="insert into buddy (fid,tid) values(:fid,:tid)";
		Query q=session.createSQLQuery(sql);
		q.setInteger("fid", master.getId());
		q.setInteger("tid", target.getId());
		System.out.println(q.executeUpdate());
		session.clear();
		session.close();
		NoticeUtil.addBuddy(master,target);
		return true;
	}

	public boolean remove(User master, User target) {
		Session session=this.getSession();
		String sql="delete from buddy where fid=:fid and tid=:tid";
		Query q=session.createSQLQuery(sql);
		q.setInteger("fid", master.getId());
		q.setInteger("tid", target.getId());
		System.out.println(q.executeUpdate());
		session.clear();
		session.close();
		NoticeUtil.removeBuddy(master,target);
		return true;
	}

	public Pager findBuddyList(User master, final String flag) {
		final Integer id=master.getId();
		final String name=master.getName();
		
		
		
		return this.getHibernateTemplate().execute(new HibernateCallback<Pager>() {

			public Pager doInHibernate(Session session) throws HibernateException,
					SQLException {
				String where="";
				
				//对于其他人的关系人，仅需要查出，当前用户是否关注他们而己，不用讨论对方是否关注当前用户
				
				if("fans".equals(flag)){
					where="on (b.tid=u.id and b.fid=:cid)  where u.id in  (select fid from buddy where tid="+id+")";
				}else if("follows".equals(flag)){
					where="on (b.fid=u.id and b.tid=:cid)  where u.id  in (select tid from buddy where fid="+id+") and u.id !="+id;
						
				}else{
					where="on (b.fid=u.id and b.tid=:cid)  where u.name like '%"+name+"%'";
				}
				
				
				Pager pager=new Pager();
				String sql1="select count(u.id) from user u left join buddy b "+where;
				
				Query qc=session.createSQLQuery(sql1).setInteger("cid", TankUtil.getCurrentUser().getId());
				String sql="select u.id,u.name,u.photo,b.fid from user u left join buddy b  "+where;
				Query q=session.createSQLQuery(sql).setInteger("cid", TankUtil.getCurrentUser().getId());
				
				pager.setRowCount(qc.uniqueResult());
				List<Object[]> ls=pager.pageQuery(q);
				
				System.out.println(ls.size());
				
				
				List<Buddy> bs=new ArrayList(ls.size());
				boolean flag=false;
				for(Object[] os:ls){
					flag=(os[3]==null)?false:true;
					bs.add(new Buddy((Integer)os[0],(String)os[1],(String)os[2],flag));					
				}
				pager.setData(bs);
				session.clear();
				session.close();

				return pager;
			}
		});
	}

	public List<Buddy> findOthersFollowsList(User master) {
		// TODO Auto-generated method stub
		return null;
	}

	public Pager doFind(User master) {
		final Integer id=master.getId();
		final String name=master.getName();
		
		
		
		return this.getHibernateTemplate().execute(new HibernateCallback<Pager>() {

			public Pager doInHibernate(Session session) throws HibernateException,
					SQLException {
				String where="";
				where="where u.name like '%"+name+"%' and u.id !="+TankUtil.getCurrentUser().getId();
				Pager pager=new Pager();
				String sql1="select count(u.id) from user u left join buddy b on (b.tid=u.id and b.fid=:cid) "+where;
				
				Query qc=session.createSQLQuery(sql1).setInteger("cid", TankUtil.getCurrentUser().getId());
				String sql="select u.id,u.name,u.photo,b.fid from user u left join buddy b on (b.tid=u.id and b.fid=:cid) "+where;
				Query q=session.createSQLQuery(sql).setInteger("cid", TankUtil.getCurrentUser().getId());
				
				pager.setRowCount(qc.uniqueResult());
				List<Object[]> ls=pager.pageQuery(q);
				
				System.out.println(ls.size());
				
				
				List<Buddy> bs=new ArrayList(ls.size());
				boolean flag=false;
				for(Object[] os:ls){
					flag=(os[3]==null)?false:true;
					bs.add(new Buddy((Integer)os[0],(String)os[1],(String)os[2],flag));					
				}
				pager.setData(bs);
				session.clear();
				session.close();

				return pager;
			}
		});
	}


}
